﻿#region License
/* Copyright (C) 2009 Tim Coulter
 * 
 * This file is part of ExtremeML.
 * 
 * ExtremeML is free software: you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation either version
 * 2 of the License, or (at your option) any later version.
 * 
 * ExtremeML is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty
 * of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
 * the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see http://www.gnu.org/licenses/.
*/
#endregion

using System;
using System.Diagnostics;
using ExtremeML.Packaging;
using ExtremeML.Spreadsheet.Address;

namespace ExtremeML.Samples
{
	public static class CellManipulation
	{
		/// <summary>
		/// Populates the Blank worksheet table with random sample data.
		/// </summary>
		/// <remarks>
		/// This method demonstrates populating worksheet cells with a grid of randomly generated
		/// strongly-typed data. Unlike the table examples, this technique writes directly to
		/// worksheet cells, so the geometry of the worksheet is not affected by row insertion.
		/// </remarks>
		public static void PopulateRandom()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var part = package.WorkbookPart.WorksheetParts["Blank"];

				for (var row = 0; row < 10; row++)
				{
					part.Worksheet.SetCellValue(new GridReference(row, 0), Utility.GetRandomString());
					part.Worksheet.SetCellValue(new GridReference(row, 1), Utility.GetRandomUri());
					part.Worksheet.SetCellValue(new GridReference(row, 2), Utility.GetRandomDateTime());
					part.Worksheet.SetCellValue(new GridReference(row, 3), Utility.GetRandomTimeSpan());
					part.Worksheet.SetCellValue(new GridReference(row, 4), Utility.GetRandomInteger());
					part.Worksheet.SetCellValue(new GridReference(row, 5), Utility.GetRandomDouble());
					part.Worksheet.SetCellValue(new GridReference(row, 6), Utility.GetRandomDecimal());
					part.Worksheet.SetCellValue(new GridReference(row, 7), Utility.GetRandomBoolean());
				}
			}
		}

		/// <summary>
		/// Writes to worksheet cells and reads back a series of strongly-typed random values.
		/// </summary>
		/// <remarks>
		/// This method demonstrates populating specified worksheet cells with a sequence of randomly
		/// generated strongly-typed values. This data is subsequently retrieved as strongly-typed values
		/// by passing the appropriate generic type argument to the WorksheetWrapper.GetCellValue() method.
		/// </remarks>
		public static void WriteAndReadValues()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var part = package.WorkbookPart.WorksheetParts["Blank"];

				// Write some row headings that identify the data types
				part.Worksheet.SetCellValue(new GridReference(0, 0), "String");
				part.Worksheet.SetCellValue(new GridReference(1, 0), "Uri");
				part.Worksheet.SetCellValue(new GridReference(2, 0), "DateTime");
				part.Worksheet.SetCellValue(new GridReference(3, 0), "TimeSpan");
				part.Worksheet.SetCellValue(new GridReference(4, 0), "Integer");
				part.Worksheet.SetCellValue(new GridReference(5, 0), "Double");
				part.Worksheet.SetCellValue(new GridReference(6, 0), "Decimal");
				part.Worksheet.SetCellValue(new GridReference(7, 0), "Boolean");

				// Write the random cell data values
				part.Worksheet.SetCellValue(new GridReference(0, 1), Utility.GetRandomString());
				part.Worksheet.SetCellValue(new GridReference(1, 1), Utility.GetRandomUri());
				part.Worksheet.SetCellValue(new GridReference(2, 1), Utility.GetRandomDateTime());
				part.Worksheet.SetCellValue(new GridReference(3, 1), Utility.GetRandomTimeSpan());
				part.Worksheet.SetCellValue(new GridReference(4, 1), Utility.GetRandomInteger());
				part.Worksheet.SetCellValue(new GridReference(5, 1), Utility.GetRandomDouble());
				part.Worksheet.SetCellValue(new GridReference(6, 1), Utility.GetRandomDecimal());
				part.Worksheet.SetCellValue(new GridReference(7, 1), Utility.GetRandomBoolean());

				// Value types must always be retrieved in their nullable form
				var stringResult = part.Worksheet.GetCellValue<string>(new GridReference(0, 1));
				var uriResult = part.Worksheet.GetCellValue<Uri>(new GridReference(1, 1));
				var dateTimeResult = part.Worksheet.GetCellValue<DateTime?>(new GridReference(2, 1));
				var timeSpanResult = part.Worksheet.GetCellValue<TimeSpan?>(new GridReference(3, 1));
				var intResult = part.Worksheet.GetCellValue<int?>(new GridReference(4, 1));
				var doubleResult = part.Worksheet.GetCellValue<double?>(new GridReference(5, 1));
				var decimalResult = part.Worksheet.GetCellValue<decimal?>(new GridReference(6, 1));
				var boolResult = part.Worksheet.GetCellValue<bool?>(new GridReference(7, 1));
			}
		}

        /// <summary>
        /// Retrieves and prints the coordinates and values of every defined cell in the target worksheet.
        /// </summary>
        /// <remarks>
        /// This method demonstrates retrieving the coordinates and value of all cells in a worksheet
        /// by iterating over the defined rows collection and the cell collections within each of them.
        /// </remarks>
        public static void IterateOverRowsAndCells()
        {
            Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

            using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
            {
                var part = package.WorkbookPart.WorksheetParts["Populated"];
                    
                foreach (var row in part.Worksheet.SheetData.Rows)
                {
                    foreach (var cell in row.Cells)
                    {
                        var rowIndex = cell.CellAddress.RowIndex;
                        var columnIndex = cell.CellAddress.ColumnIndex;
                        var value = cell.CellValue.Value;

                        Debug.WriteLine(string.Format("Row: {0}, Column: {1}, Value: {2}", rowIndex, columnIndex, value));
                    }
                }
            }
        }

        /// <summary>
        /// Retrieves the strongly typed cell values of qualifying rows in the target worksheet.
        /// </summary>
        /// <remarks>
        /// This method demonstrates iterating over the rows in worksheet, retrieving the strongly-typed
        /// values of cells in rows that fall within a range criteria.
        /// </remarks>
        public static void IterateOverRowsAndRetrieveCellValues()
        {
            Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

            using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
            {
                var part = package.WorkbookPart.WorksheetParts["Populated"];

                foreach (var row in part.Worksheet.SheetData.Rows)
                {
                    if (row.RowIndex >= 4 && row.RowIndex <= 8)
                    {
                        // Value types must always be retrieved in their nullable form
                        var name = row.GetCellValue<string>(0);
                        var uri = row.GetCellValue<Uri>(1);
                        var spring = row.GetCellValue<int?>(2);
                        var summer = row.GetCellValue<int?>(3);
                        var autumn = row.GetCellValue<int?>(4);
                        var winter = row.GetCellValue<int?>(5);
                        var total = row.GetCellValue<int?>(6);

                        Debug.WriteLine(string.Format("Name: {0}, Uri: {1}, Total: {2}", name, uri, total));
                    }
                }
            }
        }
	}
}
